<?php

require_once("config.php");

// Make connection to MySQL database
try{
	$connection_url = "mysql:host=$mysql_host;dbname=$mysql_db";
	$dbh = new PDO($connection_url, $mysql_user, $mysql_pass);
}catch(PDOException $e){
	print "Error connecting to database: ".$e->getMessage()."<br>\n";
	die();
}



//---------------------------
// PopulateQuestionsTable
//---------------------------
function PopulateQuestionsTable()
{
	global $dbh;

	// Loop over all combinations of integers from 0 to 12
	$Nentries;
	for($num1=0; $num1<=12; $num1++){
		for($num2=0; $num2<=12; $num2++){
			$answer = $num1 * $num2;
			$query = "INSERT INTO Questions (num1,num2,operator,answer) VALUES($num1,$num2,'times',$answer)";
			if($dbh->query($query) == FALSE){
				print "Error when submitting query: \"$query\"<br>\n";
				print_r($dbh->errorInfo());
				print "<br>\n";
			}
			$Nentries++;
		}
	}
	
	print "<p>Entered $Nentries questions into database</p>\n";
}

//---------------------------
// ClearQuestionsTable
//---------------------------
function ClearQuestionsTable()
{
	global $dbh;

	$stmt = $dbh->query('DELETE FROM Questions');
	if($stmt == FALSE){
		$Ndeleted = 0;
	}else{
		$Ndeleted = $stmt->rowCount();
	}

	print "<p>Deleted $Ndeleted questions from database</p>\n";
}

//---------------------------
// CreateUser
//---------------------------
function CreateUser($username, $first_name, $email, $password)
{
	global $dbh;
	
	$query = "INSERT INTO Users (username,first_name,email,password,latest_Nprob,latest_Ncorrect,latest_time,created)";
	$query .= " VALUES('$username','$first_name','$email','$password',0,0,NOW(),NOW())";
	if($dbh->query($query) == FALSE){
		print "Error when submitting query: \"$query\"<br>\n";
		print_r($dbh->errorInfo());
		print "<br>\n";
	}else{
		$_SESSION["userid"] = $dbh->lastInsertId();
	}
}

//---------------------------
// DoesUserExist
//---------------------------
function DoesUserExist($username)
{
	global $dbh;
	
	$query = "SELECT username FROM Users WHERE username='$username'";
	$stmt = $dbh->query($query);
	if($stmt->fetch()){
		return TRUE;
	}else{
		return FALSE;
	}
}


//---------------------------
// GetUserInfo
//---------------------------
function GetUserInfo($userid, &$userinfo)
{
	global $dbh;
	
	$query = "SELECT * FROM Users WHERE userid='$userid'";
	$stmt = $dbh->query($query);
	if($stmt == FALSE)return FALSE;
	
	if($userinfo = $stmt->fetch()){
		return TRUE;
	}
	
	return FALSE;
}

//---------------------------
// AuthenticateUser
//---------------------------
function AuthenticateUser($username, $password)
{
	global $dbh;
	
	$query = "SELECT * FROM Users WHERE username='$username' and password='$password'";
	$stmt = $dbh->query($query);
	if($r  = $stmt->fetch()){
		$_SESSION["userid"] = $r["userid"];
		return TRUE;
	}else{
		return FALSE;
	}
}

//---------------------------
// RecordTest
//---------------------------
function RecordTest()
{
	global $dbh;

	// Get info submitted through form
	$nrow = $_REQUEST["nrow"];
	$ncol = $_REQUEST["ncol"];
	$nprob = $nrow*$ncol;
	$start_time = $_REQUEST["start_time"];
	$end_time = time();
	$time_diff = $end_time - $start_time;
	$minutes = floor($time_diff/60.0);
	$seconds = $time_diff - $minutes*60.0;

	$userid = $_SESSION["userid"];
	
	// Make a new entry in the Stats table so we can get a unique testid for this
	$query = "INSERT INTO Stats (userid,Nprob,Ncorrect,time_seconds,created) VALUES('$userid','$nprob','-1','$time_diff',NOW())";
	$stmt = $dbh->query($query);
	if($stmt == FALSE)print_r($dbh->errorInfo())."<br>\n";
	$testid = $dbh->lastInsertId();
	
	// Loop over problems
	$ncorrect = 0;
	for($j=0; $j<$nrow; $j++){
		for($i=0; $i<$ncol; $i++){
			// Get problem from form info
			$prob = $i+$j*$ncol+1;
			$num1str = "q${prob}num1";
			$num2str = "q${prob}num2";
			
			$num1 = $_REQUEST[$num1str];
			$num2 = $_REQUEST[$num2str];
			
			$user_answer = $_REQUEST["answer${prob}"];
			
			// Get the questionid and answer
			$query = "SELECT questionid,answer FROM Questions WHERE num1=$num1 AND num2=$num2";
			$stmt = $dbh->query($query);
			if($stmt!=FALSE){
				$obj = $stmt->fetchObject();
				$questionid = $obj->questionid;
				$answer = $obj->answer;
				if(strlen($user_answer)>0 && $answer==$user_answer){
					$ncorrect++;
					$correct = 'Y';
				}else{
					$correct = 'N';
				}
				print_r($row);
				$query = "INSERT INTO Test (testid,questionid,user_answer,correct) VALUES($testid,$questionid,\"$user_answer\",$correct)";
			}
		}
	}
	
	// Update Stats table to reflect number of correct responses
	$query = "UPDATE Stats SET Ncorrect='$ncorrect' WHERE testid='$testid' ";
	$dbh->query($query);

	// Update Users table to reflect latest test results
	$query = "UPDATE Users SET latest_Nprob='$nprob',latest_Ncorrect='$ncorrect',latest_time=NOW()";
	$query .= "  WHERE userid='$userid' ";
	$dbh->query($query);
}

//---------------------------
// GetUserTestHistory
//---------------------------
function GetUserTestHistory($userid)
{
	global $dbh;

	$stmt = $dbh->query("SELECT * FROM Stats WHERE userid='$userid' ORDER BY created DESC");
	$all = $stmt->fetchAll();
	
	return $all;
}

//---------------------------
// GetFastestTimes
//---------------------------
function GetFastestTimes($userid=-1)
{
	global $dbh;

	$conditions = " WHERE Stats.userid=Users.userid AND Nprob=Ncorrect AND Nprob=100";
	if($userid!=-1)$conditions .= " AND Users.userid='$userid'";

	$query = "SELECT *,Stats.created AS time_completed FROM Stats,Users $conditions ORDER BY time_seconds";
	$stmt = $dbh->query($query);
	$all = $stmt->fetchAll();
	
	return $all;
}

//---------------------------
// GetQuestionStatusForUser
//---------------------------
function GetQuestionStatusForUser($userid, &$allowed_questions, &$not_allowed_questions, &$all_questions)
{
	global $dbh;

	// Get list of all questions
	$all_questions = array();
	$query = "SELECT questionid FROM Questions ORDER BY questionid";
	foreach($dbh->query($query) AS $row)$all_questions[] = $row["questionid"];

	// Get questions explictly listed as "allowed" for this user 
	$allowed_questions = array();
	$query  = "SELECT Questions.questionid AS questionid ";
	$query .= "FROM Questions,Users,AllowedQuestions ";
	$query .= "WHERE AllowedQuestions.userid=Users.userid ";
	$query .= "      AND Users.userid='$userid' ";
	$query .= "      AND Questions.questionid=AllowedQuestions.questionid ";
	$query .= "ORDER BY questionid";
	$stmt = $dbh->query($query);
	if($stmt != FALSE){
		foreach($stmt AS $row)$allowed_questions[] = $row["questionid"];
	}

	// Any questions not explicitly allowed must be "not allowed"
	// n.b. The special case of no allowed questions is interpreted
	// as all questions being allowed, but not here. It is up to
	// the calling routine to handle that.
	foreach($all_questions AS $questionid){
		if(! in_array($questionid, $allowed_questions)){
			$not_allowed_questions[] = $questionid;
		}
	}
	
	return;
}

//---------------------------
// GetAllQuestionIDs
//---------------------------
function GetAllQuestionIDs()
{
	global $dbh;

	// Get list of all questions
	$all_questions = array();
	$query = "SELECT questionid FROM Questions ORDER BY questionid";
	foreach($dbh->query($query) AS $row)$all_questions[] = $row["questionid"];

	return $all_questions;
}

//---------------------------
// GetQuestion
//---------------------------
function GetQuestion($questionid, &$num1, &$num2, &$operator, &$answer)
{
	global $dbh;

	$query = "SELECT * FROM Questions WHERE questionid='$questionid' LIMIT 1";
	foreach($dbh->query($query) AS $row){
		$num1 = $row["num1"];
		$num2 = $row["num2"];
		$operator = $row["operator"];
		$answer = $row["answer"];
	}
}

//---------------------------
// ReplaceAllowedQuestions
//---------------------------
function ReplaceAllowedQuestions($userid, $allowed_questions)
{
	global $dbh;

	// Delete any existing entries for this user
	$dbh->query("DELETE FROM AllowedQuestions WHERE userid='$userid'");

	// Insert the new list of allowed questions
	foreach($allowed_questions AS $questionid){
		$query = "INSERT INTO AllowedQuestions (userid,questionid,created) VALUES('$userid','$questionid',NOW())";
		$dbh->query($query);
	}
}


?>
